package fi.internetix.edelphi.liveillustrator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Utils {

  public static void sendControlCommand(String action, String args) throws SQLException {
    Connection conncetion = Utils.getConnection();
    PreparedStatement preparedStatement = conncetion.prepareStatement("INSERT INTO PUBLIC.CONTROL (id, ACTION, ARGS) values (?,?,?)");
    preparedStatement.setLong(1, System.currentTimeMillis());
    preparedStatement.setString(2, action);
    preparedStatement.setString(3, args);
    preparedStatement.execute();
    conncetion.close();
  }
  
  public static List<ControlCommand> retrieveControlCommands() throws SQLException {
    List<ControlCommand> commands = new ArrayList<ControlCommand>();
    
    Connection c = Utils.getConnection();
    c.setAutoCommit(true);
    
    PreparedStatement preparedStatement = c.prepareStatement("SELECT id, ACTION, ARGS from PUBLIC.CONTROL");
    if (preparedStatement.execute()) {
      ResultSet resultSet = preparedStatement.getResultSet();
      while (resultSet.next()) {
        commands.add(new ControlCommand(resultSet.getLong(1), resultSet.getString(2), resultSet.getString(3)));
      }
    }
    
    PreparedStatement deleteStatement = c.prepareStatement("DELETE FROM PUBLIC.CONTROL where id = ?");
    for (ControlCommand command : commands) {
      deleteStatement.setLong(1, command.getId());
      deleteStatement.execute();
    }
    
    c.close();
    
    return commands;
  }
  
  public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection("jdbc:hsqldb:mem:ldi;user=SA", "SA", "");
  }

}
